﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TireBias.Models
{
    public class TailModel
    {
        public class TailBias_DS
        {
            public string _biasno { get; set; }
            public string _plandt { get; set; }
            public string _itemno { get; set; }
            public string _sitemno { get; set; }
            public string _Ply1 { get; set; }
            public string _Ply2 { get; set; }
            public string _shift { get; set; }
            public string _P1 { get; set; }
            public string _P2 { get; set; }
            public string _P3 { get; set; }
            public string _P4 { get; set; }
            public string _topcrdcd { get; set; }
            public string _sizes { get; set; }
            public string _forming { get; set; }
            public string _Text { get; set; }
            public string _toproll { get; set; }
            public string _topexp { get; set; }
            public string _toplen { get; set; }
            public string _sampflag { get; set; }
            public string _stdSubFd { get; set; }
            public string _stdFile { get; set; }
            public string _stdModBy { get; set; }
            public string _stdModDt { get; set; }
            public string _infSubFd { get; set; }
            public string _infFile { get; set; }
            public string _infModBy { get; set; }
            public string _infModDt { get; set; }
            public string _toplot { get; set; }
            public string _STDvalid { get; set; }          
            public string _INFvalid { get; set; }
            public string _Angle { get; set; }
            public string _Width { get; set; }
            public string _Width2 { get; set; }
            public string _Delay { get; set; }
        }

        public class PlyRoll_DS
        {
            public string _biasno { get; set; }
            public string _plandt { get; set; }
            public string _itemno { get; set; }
            public string _sitemno { get; set; }
            public string _prddate { get; set; }
           
            public string _side { get; set; }
            public string _rollno { get; set; }
            public string _cartno { get; set; }
            public string _plylot { get; set; }
            public string _plyexp { get; set; }
            public string _plylen { get; set; }
            public string _Plyan1 { get; set; }
            public string _Plyan2 { get; set; }
            public string _Plyan3 { get; set; }
            public string _Plywd1 { get; set; }
            public string _Plywd2 { get; set; }
            public string _Plywd3 { get; set; }
            public string _Plymin { get; set; }
            public string _Plyjn1 { get; set; }
            public string _Plyjn2 { get; set; }
            public string _Plyjn3 { get; set; }
            public string _empname { get; set; }
            public string _shift { get; set; }
            public string _update { get; set; }
            public string _Ply1 { get; set; }
            public string _Ply2 { get; set; }

            public string _topcode { get; set; }
            public string _sizes { get; set; }
            public string _forming { get; set; }
            public string _toproll { get; set; }
            public string _topexp { get; set; }
            public string _toplen { get; set; }
            public string _topLot { get; set; }   
            public string _angel { get; set; }
            public string _tapesheet { get; set; }
            public string _topsheet { get; set; }
            public string _oldTape { get; set; }
            public string _oldSheet { get; set; }
            public string _Newside { get; set; }
            public string _oldAngle { get; set; }
        }

        public DataTable GET_EXP(string sizes ,string topcode,string forming,string dtPlan)
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select Sizes,Tirecode,Forming, ");
            str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120)) CurDate ,");
            str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.GET_SHIFT_PRD(convert(datetime,getdate(),120)) shift ");
            str.AppendLine(",month(" + Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120))) mCur ");
            str.AppendLine(",day(" + Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120))) dCur ");
            str.AppendLine(",dateadd(day,age+1,convert(datetime, " + Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120)),120)) ExpDate");

            str.AppendLine(" from " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD ");
            str.AppendLine(" where sizes=@size and tirecode=@code and forming=@forming");
            
            SqlCommand cmd = new SqlCommand(str.ToString() , cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            cmd.Parameters.Add("@size", SqlDbType.VarChar).Value = sizes;
            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;
            cmd.Parameters.Add("@forming", SqlDbType.VarChar).Value = forming;
            cmd.Parameters.Add("@dtPlan", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtPlan, WinNemoLib.DateUtil.eLanguage.EN);
            SqlDataAdapter my = new SqlDataAdapter();

            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            TailBias_DS model = new TailBias_DS();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return new DataTable();
            }

            DataRow items = ds.Tables[0].Rows[0];
            return ds.Tables[0];
        }

        public DataTable GET_SHIFT()
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select  ");
            str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120)) CurDate ,");
            str.AppendLine(Nemo.GetINI("Dbase", "Base") + ".dbo.GET_SHIFT_PRD(convert(datetime,getdate(),120)) shift ");
            str.AppendLine(",month(" + Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120))) mCur ");
            str.AppendLine(",day(" + Nemo.GetINI("Dbase", "Base") + ".dbo.GET_DATE_PRD(convert(datetime,getdate(),120))) dCur ");


            SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            SqlDataAdapter my = new SqlDataAdapter();

            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            TailBias_DS model = new TailBias_DS();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return null;
            }

            DataRow items = ds.Tables[0].Rows[0];
            return ds.Tables[0];
        }

        public List<TailBias_DS> SEL_KANBANPLAN(string biasno, string dtpln = "", string itemno = "",string sitemno="",Boolean blnStand =false,Boolean noFin=false)
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select  b.sitemno,b.ply1,b.ply2,a.* ");
            str.AppendLine(",case when ply1='P1' and P1>80 then  'true'  ");
            str.AppendLine("      when ply1='P2' and P2>80 then  'true' ");
            str.AppendLine("      when ply1='P3' and P3>80 then  'true' ");
            str.AppendLine("      when ply1='P4' and P4>80 then  'true' ");
            str.AppendLine("	else 'false' end delayshow ");

            if (blnStand)
            {
                str.AppendLine(",c.subfd stdsubfd,c.[filename] stdfile,c.modifyby stdBy,c.modifydt stddt");
                str.AppendLine(",d.subfd infsubfd,d.[filename] inffile,d.modifyby infBy,d.modifydt infdt");
                str.AppendLine(",c.validFrom stdFr,c.validTo stdTo ,d.validFrom infFr,d.validTo infTo  ");
                str.AppendLine(",case when smpemp is null then e.angle else angply1  end angle ");
                str.AppendLine(",case when smpemp is null then e.pwidth else smwidth1  end pwidth ");
                str.AppendLine(",case when smpemp is null then f.pwidth else smwidth2  end pwidth2 ");
            }
            str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN a");
            str.AppendLine(" INNER JOIN " + Nemo.GetINI("Dbase", "Base") + ".dbo.biassubplan b  ");
            str.AppendLine(" on a.biasno=b.biasno and a.plandt=b.plandt and a.itemno=b.itemno ");
            if (blnStand)
            {
                str.AppendLine(" LEFT JOIN " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD c  ");
                str.AppendLine(" on a.Sizes=c.sizes and a.topcrdcd=c.TireCode and a.Forming=c.forming ");
                str.AppendLine(" LEFT JOIN " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_INF d  ");
                str.AppendLine(" on c.tireid=d.tireid  and getdate() between c.validfrom and c.validto ");
                str.AppendLine(" LEFT JOIN (select distinct tireid,angle,ply,pwidth from  " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_des )e on c.tireid=e.tireid and 'P'+e.ply=b.ply1 ");
                str.AppendLine(" LEFT JOIN (select distinct tireid,angle,ply,pwidth from  " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_des  )f on c.tireid=f.tireid and 'P'+f.ply=b.ply2 ");

            }
            str.AppendLine(" where plansts='L'  and a.biasno=@biasno and Skpflag is null and actflag='X' ");

            if (noFin)
            {
                str.AppendLine(" and b.finflag is null ");
            }

            if (StrUtil.IsNotEmpty(dtpln))
            {
                str.AppendLine(" and a.plandt=convert(datetime,@plndt,120) ");
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                str.AppendLine(" and a.itemno=@itemno ");
            }
            if (StrUtil.IsNotEmpty(itemno))
            {
                str.AppendLine(" and b.sitemno=@sitemno ");
            }

            SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,convert(int,a.itemno),convert(int,b.sitemno) ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            if (StrUtil.IsNotEmpty(dtpln))
            {
                cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN);
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;

            }

            if (StrUtil.IsNotEmpty(sitemno))
            {
                cmd.Parameters.Add("@sitemno", SqlDbType.VarChar).Value = sitemno;

            }
            cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            List<TailBias_DS> list = new List<TailBias_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                TailBias_DS model = new TailBias_DS();
                model._biasno = items["biasno"].ToString();
                model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
                model._itemno = items["itemno"].ToString();
                model._sitemno = items["sitemno"].ToString();
                model._topcrdcd = items["topcrdcd"].ToString();
                model._sizes = items["sizes"].ToString();
                model._forming = items["forming"].ToString();
                model._Ply1 = items["Ply1"].ToString();
                model._Ply2 = items["Ply2"].ToString();
                model._Text = items["text"].ToString();
                model._P1 = items["P1"].ToString();
                model._P2 = items["P2"].ToString();
                model._P3 = items["P3"].ToString();
                model._P4 = items["P4"].ToString();
                model._toplen = items["toplen"].ToString();
                model._toplot = items["tcrdlot"].ToString();
                model._toproll = items["toproll"].ToString();
                model._sampflag = items["smpemp"].ToString();
                model._shift = items["shift"].ToString();
                model._topexp =DateUtil.ToDisplayDate(items["topexp"], DateUtil.eLanguage.EN);
                model._Delay = items["delayshow"].ToString();
                if (blnStand)
                {
                    model._stdSubFd=items["stdsubfd"].ToString();
                    model._stdFile = items["stdfile"].ToString();
                    model._stdModDt= DateUtil.ToDisplayDateTime(items["stddt"], DateUtil.eLanguage.EN);
                    model._stdModBy = items["stdBy"].ToString();
                    model._infSubFd = items["infsubfd"].ToString();
                    model._infFile = items["inffile"].ToString();
                    model._infModDt = DateUtil.ToDisplayDateTime(items["infdt"], DateUtil.eLanguage.EN);
                    model._infModBy = items["infBy"].ToString();
                    model._STDvalid = DateUtil.ToDisplayDateTime(items["stdFr"], DateUtil.eLanguage.EN) + "-" + DateUtil.ToDisplayDateTime(items["stdTo"], DateUtil.eLanguage.EN);
                    model._INFvalid = DateUtil.ToDisplayDateTime(items["infFr"], DateUtil.eLanguage.EN) + "-" + DateUtil.ToDisplayDateTime(items["infTo"], DateUtil.eLanguage.EN);
                    model._Angle = items["angle"].ToString();
                    model._Width = items["pwidth"].ToString();
                    model._Width2 = items["pwidth2"].ToString();
                }
                list.Add(model);
            }
            return list;
        }

        public TailBias_DS GET_NO_FINISH(string biasno)
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select  a.biasno, a.plandt,a.itemno,a.sitemno from  " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASSUBPLAN a ");
            str.AppendLine(" INNER JOIN "+ Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL b ");
            str.AppendLine("  on a.[biasno]=b.biasno and a.[plandt]=b.plandt and a.[itemno]=b.itemno and a.[sitemno] =b.sitemno");
            str.AppendLine(" INNER JOIN " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN c ");
            str.AppendLine("   on a.[biasno]=c.biasno and a.[plandt]=c.plandt and a.[itemno]=c.itemno ");

            str.AppendLine("  where a.finflag is null and Skpflag is null  and a.biasno=" + biasno);

          //  SqlCommand cmd = new SqlCommand(str.ToString() + " order by a.plandt,convert(int,a.itemno), convert(int,a.sitemno) desc ", cnn);
            SqlCommand cmd = new SqlCommand(str.ToString() + "group by a.biasno , a.plandt,a.itemno,a.sitemno  order by a.plandt,convert(int,a.itemno), convert(int,a.sitemno) desc ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter my = new SqlDataAdapter();

            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);

            TailBias_DS model = new TailBias_DS();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return model;
            }

            DataRow items = ds.Tables[0].Rows[0];
            model._biasno = items["biasno"].ToString();
            model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
            model._itemno = items["itemno"].ToString();
            model._sitemno = items["sitemno"].ToString();
       
            return model;
        }


        public List<PlyRoll_DS> SEL_PLYROLL(string biasno,string dtpln="", string itemno="", string sitemno="",string side="",Boolean checkNotvalue=false)
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select  ");

            str.AppendLine("  [biasno], [plandt], [itemno], [sitemno]");
            str.AppendLine(", [side], [rollno], [cartno] , [plylot], [plyexp], [plylen], [Plyan1], [Plyan2], [Plyan3]");
            str.AppendLine(" , [Plywd1], [Plywd2], [Plywd3], [Plyjn1], [Plyjn2], [Plyjn3], a.[empname], a.[shift], a.[update],a.prd_date ");
            str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL a ");
            str.AppendLine(" where a.biasno=@biasno ");

            if (checkNotvalue)
            {
                str.AppendLine("and(  plylen is null or cartno is null ");
                str.AppendLine(" or plyan1 is null or plyan2 is null or plyan3 is null  ");
                str.AppendLine(" or plywd1 is null or plywd2 is null or plywd3 is null ");
                str.AppendLine(" or plyjn1 is null or plyjn2 is null or plyjn2 is null) ");
            }

            if (StrUtil.IsNotEmpty(dtpln))
            {
                str.AppendLine(" and a.plandt=convert(datetime,@plndt,120) ");
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                str.AppendLine(" and a.itemno=@itemno ");
            }
            if (StrUtil.IsNotEmpty(sitemno))
            {
                str.AppendLine(" and a.sitemno=@sitemno ");
            }

            if (StrUtil.IsNotEmpty(side))
            {
                str.AppendLine(" and a.side=@side ");
            }

            SqlCommand cmd = new SqlCommand(str.ToString()+" order by convert(int,rollno) desc" , cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            //parameter

            if (StrUtil.IsNotEmpty(dtpln))
            {
                cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN);
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;

            }

            if (StrUtil.IsNotEmpty(sitemno))
            {
                cmd.Parameters.Add("@sitemno", SqlDbType.VarChar).Value = sitemno;

            }
            if (StrUtil.IsNotEmpty(side))
            {
                cmd.Parameters.Add("@side", SqlDbType.VarChar).Value = side;
            }

            cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<PlyRoll_DS> list = new List<PlyRoll_DS>();
            
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }

            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                PlyRoll_DS model = new PlyRoll_DS();
                model._biasno = items["biasno"].ToString();
                model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
                model._itemno = items["itemno"].ToString();
                model._sitemno = items["sitemno"].ToString();
                model._side = items["side"].ToString();
                model._rollno = items["rollno"].ToString();
                model._cartno = items["cartno"].ToString();
                model._plylot = items["plylot"].ToString();
                model._plyexp= DateUtil.ToDisplayDate(items["plyexp"], DateUtil.eLanguage.EN);
                model._plylen = items["plylen"].ToString();
                model._Plyan1 = items["plyan1"].ToString();
                model._Plyan2 = items["plyan2"].ToString();
                model._Plyan3 = items["plyan3"].ToString();
                model._Plywd1 = items["plywd1"].ToString();
                model._Plywd2 = items["plywd2"].ToString();
                model._Plywd3 = items["plywd3"].ToString();
                model._Plyjn1 = items["Plyjn1"].ToString();
                model._Plyjn2 = items["Plyjn2"].ToString();
                model._Plyjn3 = items["Plyjn3"].ToString();
                model._empname = items["empname"].ToString();
                model._shift = items["shift"].ToString();
                model._update = DateUtil.ToDisplayDateTime(items["update"], DateUtil.eLanguage.EN);
                model._prddate = DateUtil.ToDisplayDateTime(items["prd_date"], DateUtil.eLanguage.EN);
               
                list.Add(model);

            }
            return list;
        }


        public List<TailBias_DS> SUM_PLYROLL(string biasno, string dtpln, string itemno)
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select  ");       
            str.AppendLine(" case when sP1>=P1 then P1 else sP1 end sumP1");
            str.AppendLine(" ,case when sP2>=P2 then P2 else sP2 end sumP2");
            str.AppendLine(" ,case when sP3>=P3 then P3 else sP3 end sumP3");
            str.AppendLine(" ,case when sP4>=P4 then P4 else sP4 end sumP4 from ( ");
            str.AppendLine("select  biasno,plandt,itemno");
            str.AppendLine(",sum(case when side='P1' then plylen else 0 end) sP1");
            str.AppendLine(",sum(case when side='P2' then plylen else 0 end) sP2");
            str.AppendLine(",sum(case when side='P3' then plylen else 0 end) sP3");
            str.AppendLine(",sum(case when side='P4' then plylen else 0 end) sP4 from ( ");

            str.AppendLine("select t.biasno,t.plandt,t.itemno,t.sitemno, case when side='L' then ply1 when side='R' then ply2 else side end side ,sum(plylen) plylen  from " + Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL b ");
            str.AppendLine(" INNER JOIN  " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASsubPLAN t ");
            str.AppendLine(" on  t.biasno=b.biasno and t.plandt=b.plandt and  t.itemno=b.itemno and t.sitemno=b.sitemno ");
            str.AppendLine(" group by  t.biasno,t.plandt,t.itemno,t.sitemno,side ,ply1,ply2 )a where a.biasno=@biasno  and a.plandt=convert(datetime,@plndt,120) and a.itemno=@itemno   ");
            str.AppendLine(" group by biasno,plandt,itemno  ) b INNER JOIN  " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN t ");
             str.AppendLine(" on  t.biasno=b.biasno and t.plandt=b.plandt and  t.itemno=b.itemno ");


            SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            //parameter

            if (StrUtil.IsNotEmpty(dtpln))
            {
                cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN);
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;

            }

            cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<TailBias_DS> list = new List<TailBias_DS>();

            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }

            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                TailBias_DS model = new TailBias_DS();
                model._P1 = items["sumP1"].ToString();
                model._P2 = items["sumP2"].ToString();
                model._P3 = items["sumP3"].ToString();
                model._P4 = items["sumP4"].ToString();
                list.Add(model);

            }
            return list;
        }



        public List<PlyRoll_DS> PRINT_PLY(string biasno, string dtpln, string itemno, string sitemno,string side,string rows)
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select  ");
            str.AppendLine("c.topcrdcd,c.sizes,c.forming,c.tcrdlot toplot,c.toproll,topexp,toplen,");
            str.AppendLine("a.biasno,a.plandt,a.itemno,a.sitemno,a.rollno,cartno");
            str.AppendLine(",case when a.side='L' then Ply1 when a.side='R' then Ply2 else side end side ");
            str.AppendLine(",plylot,plyexp,plylen,a.empname,a.shift, angPly1 ");
            str.AppendLine(",case when side='L' then angPly1 else angPly2 end Angle");
            str.AppendLine(",b.*,case when s.tapesheet=1 then 1 else null end tapesheet,case when s.topsheet=1 then 1 else null end topsheet");

            str.AppendLine(",Case When Plywd1 < Plywd2 And Plywd1 < Plywd3 Then Plywd1");
            str.AppendLine("When Plywd2 < Plywd1 And Plywd1 < Plywd3 Then Plywd2 ");
            str.AppendLine("Else Plywd3 End As PlyMin");

            str.AppendLine("from  " + Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL a");
            str.AppendLine("LEFT JOIN   " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASSUBPLAN b ");
            str.AppendLine("on");
            str.AppendLine("a.biasno=b.biasno and a.plandt=b.plandt ");
            str.AppendLine("and a.itemno=b.itemno ");
            str.AppendLine("and a.sitemno = b.sitemno");
            str.AppendLine("LEFT JOIN  " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN c on ");
            str.AppendLine("a.biasno=c.biasno and a.plandt=c.plandt ");
            str.AppendLine("and a.itemno=c.itemno");
            str.AppendLine("LEFT JOIN ");
            str.AppendLine("(");
            str.AppendLine("select a.sizes,a.tirecode,a.forming,'P'+ply ply,tapesheet,topsheet");
            str.AppendLine("from  " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD a");
            str.AppendLine("INNER JOIN  " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_DES b on a.tireid=b.tireid");
            str.AppendLine("group by  a.sizes,a.tirecode,a.forming,tapesheet,topsheet,ply");
            str.AppendLine(")s on c.topcrdcd=s.tirecode and c.sizes=s.sizes and c.forming =s.forming and  (case when a.side='L' then Ply1 when a.side='R' then Ply2 else side end )=s.ply");
            str.AppendLine(" where a.biasno=@biasno  and a.plandt=convert(datetime,@plndt,120) and a.itemno=@itemno and a.sitemno=@sitemno and a.side=@side");
            str.AppendLine(" and a.rollno=@row");


            SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            //parameter

            if (StrUtil.IsNotEmpty(dtpln))
            {
                cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN);
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;
            }

            if (StrUtil.IsNotEmpty(sitemno))
            {
                cmd.Parameters.Add("@sitemno", SqlDbType.VarChar).Value = sitemno;
            }
            cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;
            cmd.Parameters.Add("@side", SqlDbType.VarChar).Value = side;
            cmd.Parameters.Add("@row", SqlDbType.VarChar).Value = rows;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<PlyRoll_DS> list = new List<PlyRoll_DS>();

            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }

            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                PlyRoll_DS model = new PlyRoll_DS();
                model._topcode = items["topcrdcd"].ToString();
                model._sizes = items["sizes"].ToString();
                model._forming = items["forming"].ToString();
                model._topLot = items["toplot"].ToString();
                model._topexp = items["topexp"].ToString();
                model._toplen = items["toplen"].ToString();
                model._toproll = items["toproll"].ToString();
                model._biasno = items["biasno"].ToString();
                model._plandt = DateUtil.ToDisplayDate(items["plandt"], DateUtil.eLanguage.EN);
                model._itemno = items["itemno"].ToString();
                model._sitemno = items["sitemno"].ToString();
                model._side = items["side"].ToString();
                model._rollno = items["rollno"].ToString();
                model._cartno = items["cartno"].ToString();
                model._plylot = items["plylot"].ToString();
                model._plyexp = DateUtil.ToDisplayDate(items["plyexp"], DateUtil.eLanguage.EN);
                model._plylen = items["plylen"].ToString();
                model._empname = items["empname"].ToString();
                model._shift = items["shift"].ToString();
                model._angel = items["angPly1"].ToString();
                model._tapesheet = items["tapesheet"].ToString();
                model._topsheet = items["topsheet"].ToString();
                model._Plymin = items["PlyMin"].ToString();
                list.Add(model);

            }
            return list;
        }

        public DataTable PRINT_PLY_DT(string biasno, string dtpln, string itemno, string sitemno, string side, string rows)
        {
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            str.AppendLine(" select  ");
            str.AppendLine("c.topcrdcd,c.sizes,c.forming,c.tcrdlot toplot,c.toproll,topexp,toplen,");
            str.AppendLine("a.biasno,a.plandt,a.itemno,a.sitemno,a.rollno,cartno");
            str.AppendLine(",case when a.side='L' then Ply1 when a.side='R' then Ply2 else side end side ");
            str.AppendLine(",plylot,plyexp,plylen,a.empname,a.shift, angPly1 ");
            str.AppendLine(",case when side='L' then angPly1 else angPly2 end Angle");
            str.AppendLine(",b.*,case when s.tapesheet=1 then 1 else null end tapesheet,case when s.topsheet=1 then 1 else null end topsheet");

            str.AppendLine(",Case When Plywd1 < Plywd2 And Plywd1 < Plywd3 Then Plywd1");
            str.AppendLine("When Plywd2 < Plywd1 And Plywd1 < Plywd3 Then Plywd2 ");
            str.AppendLine("Else Plywd3 End As PlyMin");

            str.AppendLine("from  " + Nemo.GetINI("Dbase", "Base") + ".dbo.PLYROLL a");
            str.AppendLine("LEFT JOIN   " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASSUBPLAN b ");
            str.AppendLine("on");
            str.AppendLine("a.biasno=b.biasno and a.plandt=b.plandt ");
            str.AppendLine("and a.itemno=b.itemno ");
            str.AppendLine("and a.sitemno = b.sitemno");
            str.AppendLine("LEFT JOIN  " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASPLAN c on ");
            str.AppendLine("a.biasno=c.biasno and a.plandt=c.plandt ");
            str.AppendLine("and a.itemno=c.itemno");
            str.AppendLine("LEFT JOIN ");
            str.AppendLine("(");
            str.AppendLine("select a.sizes,a.tirecode,a.forming,'P'+ply ply,tapesheet,topsheet");
            str.AppendLine("from  " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD a");
            str.AppendLine("INNER JOIN  " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_DES b on a.tireid=b.tireid");
            str.AppendLine("and getdate() between a.validFrom and a.validTo ");
            str.AppendLine("group by  a.sizes,a.tirecode,a.forming,tapesheet,topsheet,ply");
            str.AppendLine(")s on c.topcrdcd=s.tirecode and c.sizes=s.sizes and c.forming =s.forming and  (case when a.side='L' then Ply1 when a.side='R' then Ply2 else side end )=s.ply");
            str.AppendLine(" where a.biasno=@biasno  and a.plandt=convert(datetime,@plndt,120) and a.itemno=@itemno and a.sitemno=@sitemno and a.side=@side");
            str.AppendLine(" and a.rollno=@row");


            SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;

            //parameter

            if (StrUtil.IsNotEmpty(dtpln))
            {
                cmd.Parameters.Add("@plndt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN);
            }

            if (StrUtil.IsNotEmpty(itemno))
            {
                cmd.Parameters.Add("@itemno", SqlDbType.VarChar).Value = itemno;
            }

            if (StrUtil.IsNotEmpty(sitemno))
            {
                cmd.Parameters.Add("@sitemno", SqlDbType.VarChar).Value = sitemno;
            }
            cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;
            cmd.Parameters.Add("@side", SqlDbType.VarChar).Value = side;
            cmd.Parameters.Add("@row", SqlDbType.VarChar).Value = rows;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<PlyRoll_DS> list = new List<PlyRoll_DS>();

            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return new DataTable();
            }
            return ds.Tables[0];
        }

        public Boolean DEL_PLYROLL(ConnectionWrapper cnn, string biasno, string dtpln, string itemno , string sitemno, string side,string rollno)
        {
            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" DELETE ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[PLYROLL]");
                comstr.AppendLine("where plandt=convert(datetime,@plndt,120) and biasno=@biasno ");
                comstr.AppendLine("and itemno=@itemno and sitemno=@sitemno and side=@side and rollno=@rollno");


                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@biasno", biasno);
                cmd.AddParameterStringWithValue("@itemno", itemno);
                cmd.AddParameterStringWithValue("@sitemno", sitemno);
                cmd.AddParameterStringWithValue("@side", side);
                cmd.AddParameterStringWithValue("@rollno", rollno);

                cmd.ExecuteNonQuery();

                // Logdata
                comstr = new StringBuilder();
                comstr.AppendLine(" DELETE ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Log") + ".[dbo].[PLYROLL]");
                comstr.AppendLine("where plandt=convert(datetime,@plndt,120) and biasno=@biasno ");
                comstr.AppendLine("and itemno=@itemno and sitemno=@sitemno and side=@side and rollno=@rollno");


                cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@biasno", biasno);
                cmd.AddParameterStringWithValue("@itemno", itemno);
                cmd.AddParameterStringWithValue("@sitemno", sitemno);
                cmd.AddParameterStringWithValue("@side", side);
                cmd.AddParameterStringWithValue("@rollno", rollno);

                cmd.ExecuteNonQuery();


                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }

        public Boolean Ins_Data(ConnectionWrapper cnn, PlyRoll_DS incoming)
        {
            try
            {  
                Nemo.Enter();
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[PLYROLL]");

                comstr.AppendLine("([biasno], [plandt], [itemno], [sitemno], [side] ");
                comstr.AppendLine(" ,[rollno], [cartno], [plylot], [plyexp], [plylen], [Plyan1], [Plyan2], [Plyan3] ");
                comstr.AppendLine(" ,[Plywd1], [Plywd2], [Plywd3], [Plyjn1], [Plyjn2], [Plyjn3], [empname], [shift], [update],prd_date) ");

                comstr.AppendLine("VALUES(@biasno,convert(datetime, @plandt,120), @itemno, @sitemno, @side");
                comstr.AppendLine(", @rollno, @cartno, @plylot,convert(datetime, @plyexp,120) , @plylen, @Plyan1, @Plyan2, @Plyan3");
                comstr.AppendLine(", @Plywd1, @Plywd2, @Plywd3, @Plyjn1, @Plyjn2, @Plyjn3");
                comstr.AppendLine(", @empname," + Nemo.GetINI("Dbase", "Base") + ".[dbo].GET_SHIFT_PRD(getdate()) , getdate(), ");
                comstr.AppendLine("convert(datetime, @prd_date,120) )");
                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();


                cmd.AddParameterStringWithValue("@biasno", incoming._biasno.Trim());
                cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._plandt, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@itemno", incoming._itemno);
                cmd.AddParameterStringWithValue("@sitemno", incoming._sitemno);

                cmd.AddParameterStringWithValue("@side", incoming._side);
                cmd.AddParameterStringWithValue("@rollno", incoming._rollno);
                cmd.AddParameterStringWithValue("@cartno", incoming._cartno);
                cmd.AddParameterStringWithValue("@plylot", incoming._plylot);
                cmd.AddParameterStringWithValue("@plyexp", DateUtil.ToInternalDate(incoming._plyexp, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@plylen", incoming._plylen);
                cmd.AddParameterStringWithValue("@Plyan1", incoming._Plyan1);
                cmd.AddParameterStringWithValue("@Plyan2", incoming._Plyan2);
                cmd.AddParameterStringWithValue("@Plyan3", incoming._Plyan3);
                cmd.AddParameterStringWithValue("@Plywd1", incoming._Plywd1);
                cmd.AddParameterStringWithValue("@Plywd2", incoming._Plywd2);
                cmd.AddParameterStringWithValue("@Plywd3", incoming._Plywd3);
                cmd.AddParameterStringWithValue("@Plyjn1", incoming._Plyjn1);
                cmd.AddParameterStringWithValue("@Plyjn2", incoming._Plyjn2);
                cmd.AddParameterStringWithValue("@Plyjn3", incoming._Plyjn3);
                cmd.AddParameterStringWithValue("@prd_date", DateUtil.ToInternalDate(incoming._prddate, WinNemoLib.DateUtil.eLanguage.EN));
       
                cmd.AddParameterStringWithValue("@empname", incoming._empname);

              
                
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    
                    cnn.RollbackTransaction();
                    return false;
                }

                comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Log") + ".[dbo].[PLYROLL]");
                
                comstr.AppendLine(" select * from ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[PLYROLL]");
                comstr.AppendLine(" where [biasno]=@biasno and  [plandt]=convert(datetime, @plandt,120) and [itemno]= @itemno ");
                comstr.AppendLine(" and [sitemno]= @sitemno and side= @side and [rollno]=@rollno");

                cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@biasno", incoming._biasno.Trim());
                cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._plandt, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@itemno", incoming._itemno);
                cmd.AddParameterStringWithValue("@sitemno", incoming._sitemno);

                cmd.AddParameterStringWithValue("@side", incoming._side);
                cmd.AddParameterStringWithValue("@rollno", incoming._rollno);

                if (cmd.ExecuteNonQuery() <= 0)
                {

                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
                throw ex;
            }

        }

        public Boolean Ins_MatToMat(ConnectionWrapper cnn, PlyRoll_DS incoming)
        {
            try
            {
                Nemo.Enter();
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" INSERT INTO ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[MatToMat]");

                comstr.AppendLine("([biasno],[plandt], [itemno],sitemno, [topcrdcd],side,oldAngle ");
                comstr.AppendLine(" ,roll, [NewSide], [newSizes], [newForming], [newAngle], [newWidth], [newLen], [newLot], [newExp], [newRub], [newTape],oldRub,oldTape,modi_by,modi_date)");


                comstr.AppendLine("VALUES(@biasno,convert(datetime, @plandt,120), @itemno,@sitemno, @topcrdcd,@side, @angle,@rollno, @Newside, @newSizes ");
                comstr.AppendLine(", @newForming, @newAngle, @newWidth, @newLen, @newLot,convert(datetime, @newExp,120)  ");
                comstr.AppendLine(", @newRub, @newTape,@oldRub,@oldTape");
                comstr.AppendLine(", @empname, getdate())");
                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();


                cmd.AddParameterStringWithValue("@biasno", incoming._biasno.Trim());
                cmd.AddParameterStringWithValue("@plandt", DateUtil.ToInternalDate(incoming._plandt, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@itemno", incoming._itemno);
                cmd.AddParameterStringWithValue("@sitemno", incoming._sitemno);
                cmd.AddParameterStringWithValue("@topcrdcd", incoming._topcode);
                cmd.AddParameterStringWithValue("@Newside", incoming._Newside.ToUpper());
                cmd.AddParameterStringWithValue("@side", incoming._side.ToUpper());//ply

                cmd.AddParameterStringWithValue("@newSizes", incoming._sizes);
                cmd.AddParameterStringWithValue("@newForming",  incoming._forming.ToUpper());
                cmd.AddParameterStringWithValue("@newAngle", incoming._angel);
                cmd.AddParameterStringWithValue("@newWidth", incoming._toplen);
                cmd.AddParameterStringWithValue("@newLen", incoming._plylen);
                cmd.AddParameterStringWithValue("@newLot", incoming._plylot.ToUpper());
                cmd.AddParameterStringWithValue("@newExp", DateUtil.ToInternalDate(incoming._plyexp, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@newRub", incoming._topsheet);
                cmd.AddParameterStringWithValue("@newTape", incoming._tapesheet);
                cmd.AddParameterStringWithValue("@oldRub", incoming._oldSheet);
                cmd.AddParameterStringWithValue("@oldTape", incoming._oldTape);
                cmd.AddParameterStringWithValue("@angle", incoming._oldAngle);

                cmd.AddParameterStringWithValue("@empname", incoming._empname);
                cmd.AddParameterStringWithValue("@rollno", incoming._rollno);



                if (cmd.ExecuteNonQuery() <= 0)
                {

                    cnn.RollbackTransaction();
                    return false;
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
                throw ex;
            }

        }

        public Boolean DEL_MatToMat(ConnectionWrapper cnn, string biasno, string dtpln, string itemno, string sitemno,string side,string rollno)
        {
            try
            {
                StringBuilder comstr = new StringBuilder();
                comstr.AppendLine(" DELETE ");
                comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[MatToMat]");
                comstr.AppendLine("where plandt=convert(datetime,@plndt,120) and biasno=@biasno ");
                comstr.AppendLine("and itemno=@itemno and sitemno=@sitemno and side=@side and roll=@rollno ");


                CommandWrapper cmd = cnn.CreateCommand();
                cmd.CommandText = comstr.ToString();
                cmd.AddParameterStringWithValue("@plndt", DateUtil.ToInternalDate(dtpln, WinNemoLib.DateUtil.eLanguage.EN));
                cmd.AddParameterStringWithValue("@biasno", biasno);
                cmd.AddParameterStringWithValue("@itemno", itemno);
                cmd.AddParameterStringWithValue("@sitemno", sitemno);
                cmd.AddParameterStringWithValue("@side", side);
                cmd.AddParameterStringWithValue("@rollno", rollno);
          
                cmd.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }

        }

    }


}